import { BaseModel, ModelAttributes, WhereCondition, OrderBy, PaginationOptions } from './BaseModel';
import { query } from '../config/database';

export interface UserCharacterRelationAttributes {
  id?: number;
  user_id: number;
  character_id: number;
  favorite_level?: number; // 1-5 星级评分
  last_interaction_at?: Date;
  created_at?: Date;
  updated_at?: Date;
}

export class UserCharacterRelation extends BaseModel {
  constructor() {
    super('user_character_relation');
  }

  
  /**
   * 查找用户与角色的关系（私有方法）
   * @param userId 用户ID
   * @param characterId 角色ID
   */
  private async findRelation(userId: number, characterId: number): Promise<UserCharacterRelationAttributes | null> {
    return await this.findOne({ user_id: userId, character_id: characterId });
  }

  
  /**
   * 更新最后互动时间
   * @param userId 用户ID
   * @param characterId 角色ID
   */
  async updateLastInteractionTime(
    userId: number,
    characterId: number
  ): Promise<UserCharacterRelationAttributes | null> {
    // 检查关系是否存在
    let relation = await this.findRelation(userId, characterId);

    if (!relation) {
      // 如果关系不存在，创建新关系
      relation = await this.create({
        user_id: userId,
        character_id: characterId,
        last_interaction_at: new Date()
      });
    } else {
      // 更新现有关系
      await this.updateMany(
        { user_id: userId, character_id: characterId },
        { last_interaction_at: new Date() }
      );
      relation = await this.findRelation(userId, characterId);
    }

    return relation;
  }

  /**
   * 获取用户收藏统计
   * @param userId 用户ID
   */
  async getUserFavoriteStats(userId: number): Promise<any> {
    const sql = `
      SELECT 
        COUNT(*) as total_characters,
        AVG(favorite_level) as avg_favorite_level,
        COUNT(CASE WHEN favorite_level >= 4 THEN 1 END) as high_favorite_count,
        MAX(last_interaction_at) as last_interaction
      FROM ${this.tableName}
      WHERE user_id = ?
    `;
    
    const result = await query(sql, [userId]);
    return result[0];
  }
}